﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Module.EntitiesInfo;

namespace Module.DataAccess
{
	public class CMSFootballPlayerDB
	{
		#region ConnectionString
        private string _connectionString;
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        public CMSFootballPlayerDB()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["localsql"].ToString();
        }
        #endregion

		#region Methods		
		public virtual int Insert(CMSFootballPlayerInfo cMSFootballPlayerInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@FullName", cMSFootballPlayerInfo.FullName),
				new SqlParameter("@DateBrithday", cMSFootballPlayerInfo.DateBrithday),
				new SqlParameter("@NationalId", cMSFootballPlayerInfo.NationalId),
				new SqlParameter("@ClubId", cMSFootballPlayerInfo.ClubId),
				new SqlParameter("@Teaser", cMSFootballPlayerInfo.Teaser),
				new SqlParameter("@Contents", cMSFootballPlayerInfo.Contents),
				new SqlParameter("@Avatar", cMSFootballPlayerInfo.Avatar),
				new SqlParameter("@Status", cMSFootballPlayerInfo.Status),
				new SqlParameter("@ShowType", cMSFootballPlayerInfo.ShowType)
			};

			return (int) SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Insert", parameters);
		}
		
		public virtual void Update(CMSFootballPlayerInfo cMSFootballPlayerInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@FootBallId", cMSFootballPlayerInfo.FootBallId),
				new SqlParameter("@FullName", cMSFootballPlayerInfo.FullName),
				new SqlParameter("@DateBrithday", cMSFootballPlayerInfo.DateBrithday),
				new SqlParameter("@NationalId", cMSFootballPlayerInfo.NationalId),
				new SqlParameter("@ClubId", cMSFootballPlayerInfo.ClubId),
				new SqlParameter("@Teaser", cMSFootballPlayerInfo.Teaser),
				new SqlParameter("@Contents", cMSFootballPlayerInfo.Contents),
				new SqlParameter("@Avatar", cMSFootballPlayerInfo.Avatar),
				new SqlParameter("@Status", cMSFootballPlayerInfo.Status),
				new SqlParameter("@ShowType", cMSFootballPlayerInfo.ShowType)
			};

			SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Update", parameters);
		}
		
		public virtual void Delete(int footBallId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@FootBallId", footBallId)
			};

			SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Delete", parameters);
		}

        public virtual IDataReader GetInfo(int footBallId)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@FootBallId", footBallId)				
			};
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Select", parameters);

        }

        /// <summary>
        /// Tính tổng số bản ghi
        /// </summary>
        /// <param name="keysearch">Từ khóa tìm kiếm</param>
        /// <param name="starDatetime">Ngày bắt đầu</param>
        /// <param name="endDatetime">Ngày kết thúc</param>
        /// <param name="beginRow">Bản ghi đầu tiền</param>
        /// <param name="endRow">Bản ghi cuối</param>
        /// <returns></returns>
        public virtual int GetCount(string keysearch,int clubId,int status, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                          
               new SqlParameter("@keysearch", keysearch),  
               new SqlParameter("@ClubId",clubId), 
               new SqlParameter("@Status",status),   
               new SqlParameter("@starDatetime", starDatetime),
               new SqlParameter("@endDatetime", endDatetime),
               new SqlParameter("@IsTotalRow", 1)
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Search", parameters);
        }

        /// <summary>
        /// Tìm kiếm
        /// </summary>
        /// <param name="keysearch">Từ khóa tìm kiếm</param>
       /// <param name="starDatetime">Ngày bắt đầu</param>
        /// <param name="endDatetime">Ngày kết thúc</param>
        /// <param name="beginRow">Bản ghi đầu</param>
        /// <param name="endRow">Bản ghi cuối</param>
        /// <param name="orderByColumn">Sắp xếp</param>
        /// <returns></returns>
        public virtual DataTable Search(string keysearch,int clubId,int status, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow, string orderByColumn)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                
                new SqlParameter("@Keysearch", keysearch),  
                new SqlParameter("@ClubId",clubId), 
                new SqlParameter("@Status",status), 
                new SqlParameter("@StarDatetime", starDatetime),
                new SqlParameter("@EndDatetime", endDatetime),               
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
				new SqlParameter("@IsTotalRow", 0)
			};

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Search", parameters).Tables[0];
        }

        //Update Trạng thái 
        public virtual void UpdateStatus(int footBallId, int status, int param)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@FootBallId", footBallId),
                new SqlParameter("@Status", status),
                new SqlParameter("@Param", param),
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_UpdateStatus", parameters);
        }               
		#endregion

        #region Outsite
        public virtual int GetCount()
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                                         
               new SqlParameter("@IsTotalRow", 1)
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Outsite", parameters);
        }
        public virtual DataTable Getlist(int beginRow, int endRow, string orderByColumn)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                                              
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
				new SqlParameter("@IsTotalRow", 0)
			};

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "CMS_FootballPlayer_Outsite", parameters).Tables[0];
        }
        #endregion
    }
}
